﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Collections;


namespace LI4SilverWCF.data
{

    class DBPontos
    {
        OdbcConnection con;
        public DBPontos(OdbcConnection c)
        {
            con = c;
        }

        public bool existeCodigo(String codigo)
        {
            string query = "SELECT ID_PONTO FROM CODIGOS_PONTOS WHERE COD_PONTO = '" + codigo + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool existe = false;

            try
            {
                if (reader.HasRows) existe = true;
            }
            catch (Exception e)
            {
            }
            con.Close();
            return existe;
        }

        public void guardarCodigo(String codigo)
        {
            string query = "INSERT INTO CODIGOS_PONTOS (COD_PONTO, DATA_ADD) VALUES ('" + codigo + "', CURRENT_TIMESTAMP)";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO INSERIR CODIGO DE PONTOS: " + e);
            }
            con.Close();
        }

        public bool codigoExisteENuncaUsado(String codigo) //verifica se o codigo existe e s ainda nao foi atribuido
        {
            string query = "SELECT ID_PONTO, DATA_UTIL FROM CODIGOS_PONTOS WHERE COD_PONTO = '" + codigo + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool usado = false;

            try
            {
                if (reader.HasRows)
                    usado = reader.IsDBNull(1);
            }
            catch (Exception e)
            {
            }
            con.Close();
            return usado;
        }

        public void apagarCodigo(String username, String codigo) //este metodo nao apaga o codigo, atribui o codigo a um cliente
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            int cod_cliente = 0;
            try
            {
                while (reader.Read())
                    cod_cliente = reader.GetInt32(0);
            }
            catch (Exception e)
            {
            }
            con.Close();

            query = "UPDATE CODIGOS_PONTOS SET COD_CLIENTE = " + cod_cliente + ", DATA_UTIL = CURRENT_TIMESTAMP WHERE COD_PONTO = '" + codigo + "'";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ATRIBUIR CODIGO DE PONTOS: " + e);
            }
            con.Close();

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES (" + cod_cliente + ", CURRENT_TIMESTAMP, 'Associacao de Codigo ao cliente')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();

        }
    }
}